package sv.com.seguros.web.controller;

import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FontFormatting;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PatternFormatting;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import sv.com.seguros.exception.WebException;
import sv.com.seguros.web.controller.asg.AsgSeguroForm;
import sv.com.seguros.web.util.DBContext;
import sv.com.seguros.web.util.WebUtilidades;

@Controller
@RequestMapping("/reportes")
public class AsgReporteController extends BaseController{
	private static final long serialVersionUID = 1695825696333740354L;

	@RequestMapping(value = "/", method = {RequestMethod.GET, RequestMethod.POST})
	public String _() {
		return "reporte/reportes";
	}
	
	@RequestMapping(value = "/view", method = {RequestMethod.GET, RequestMethod.POST})
	public String view(@RequestParam String reporte, ModelMap model) {
		try {
			if("asegurados".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "ASEGURADOS");
				return "reporte/asegurados";
			}else if("asesores".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "ASESORES");
				model.put("ctgAseguradoras", JSONArray.fromObject(DBContext.getCtgAseguradoraDao().findAllActivesAsArray()));
				model.put("ctgAsesores", JSONArray.fromObject(DBContext.getCtgAsesorDao().findAllActivesAsArray()));
				return "reporte/asesores";
			}else if("estadoCuenta".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "ESTADO DE CUENTA");
				Calendar calendar = Calendar.getInstance();
				calendar.set(Calendar.DAY_OF_MONTH, 1);
				model.put("asgFechaInicio1", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
				model.put("asgFechaInicio2", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				return "reporte/estadoCuenta";
			}else if("pendientes".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "PENDIENTES");
				Calendar calendar = Calendar.getInstance();
				calendar.set(Calendar.DAY_OF_MONTH, 1);
				model.put("asgFechaInicio1", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
				model.put("asgFechaInicio2", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				return "reporte/pendiente";
			}else if("reclamos".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "RECLAMOS");
				Calendar calendar = Calendar.getInstance();
				calendar.set(Calendar.DAY_OF_MONTH, 1);
				model.put("asgFechaInicio1", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
				model.put("asgFechaInicio2", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				return "reporte/reclamo";
			}else if("ventas".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "VENTAS");
				Calendar calendar = Calendar.getInstance();
				calendar.set(Calendar.DAY_OF_MONTH, 1);
				model.put("asgFechaInicio1", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
				model.put("asgFechaInicio2", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				return "reporte/venta";
			}else if("polizaAsesor".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "POLIZAS POR ASESOR");
				Calendar calendar = Calendar.getInstance();
				calendar.set(Calendar.DAY_OF_MONTH, 1);
				model.put("asgFechaInicio1", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
				model.put("asgFechaInicio2", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				model.put("ctgAseguradoras", JSONArray.fromObject(DBContext.getCtgAseguradoraDao().findAllActivesAsArray()));
				model.put("ctgAsesores", JSONArray.fromObject(DBContext.getCtgAsesorDao().findAllActivesAsArray()));
				return "reporte/polizaAsesor";
			}else if("polizaCliente".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "POLIZAS POR CLIENTES");
				Calendar calendar = Calendar.getInstance();
				calendar.set(Calendar.DAY_OF_MONTH, 1);
				model.put("asgFechaInicio1", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
				model.put("asgFechaInicio2", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				return "reporte/polizaCliente";
			}else if("polizaVendidas".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "POLIZAS VENDIDAS");
				Calendar calendar = Calendar.getInstance();
				calendar.set(Calendar.DAY_OF_MONTH, 1);
				model.put("asgFechaInicio1", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
				model.put("asgFechaInicio2", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				return "reporte/polizaVendidas";
			}else if("vigencia".equals(reporte)){
				model.put(WebUtilidades.HEADER_TITLE, "VIGENCIA DE CLIENTES");
				Calendar calendar = Calendar.getInstance();
				calendar.set(Calendar.DAY_OF_MONTH, 1);
				model.put("asgFechaInicio1", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
				model.put("asgFechaInicio2", WebUtilidades.dateFormatAsDD_MM_YYYY.format(calendar.getTime()));
				model.put("ctgAseguradoras", JSONArray.fromObject(DBContext.getCtgAseguradoraDao().findAllActivesAsArray()));
				return "reporte/vigencia";
			}
			
		} catch (WebException e) {
			logger.error("Exception: ", e);
		}
		return "reporte/reportes";
	}
	
	@RequestMapping(value = "/vigenciaClientes", method = {RequestMethod.GET, RequestMethod.POST})
	public void vigenciaClientes(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		String[] headers = {
    		"FECHA DE VIGENCIA", "CLIENTE", "POLIZA", "ASEGURADORA", "TIPO DE SEGURO", 
    		"CUOTAS", "PRIMA ANUAL", "VIGENCIA", "ESTADO", "ASESOR" 
    	};

		try{
	    	CreationHelper createHelper = workbook.getCreationHelper();
	    	Sheet sheet = workbook.createSheet("VIGENCIA DE CLIENTES");
	    	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
	    	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
	    	Row row = sheet.createRow(0);
	    	Cell cell = row.createCell(0);
	    	RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. VIGENCIA DE CLIENTES");
	    	Font font = workbook.createFont();
	    	HSSFCellStyle cellStyle = workbook.createCellStyle();
	    	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)18);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(1);
	    	cell = row.createCell(0);
	    	text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
	    			" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(2);
	    	for (String header : headers) {
	    		sheet.autoSizeColumn(cellNumber);
		    	cell = row.createCell(cellNumber++);
		    	text = createHelper.createRichTextString(header);
		    	text.applyFont(font);
		    	cell.setCellValue(text);
		    	cell.setCellStyle(cellStyle);
			}
	    	List<Object[]> data = DBContext.getAsgReporteDao().findVigenciaClientes(asgSeguroForm);
	    	cellNumber = 0;
	    	if(WebUtilidades.isEmptyList(data)){
	    		row = sheet.createRow(3);
	    		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
	    		cell = row.createCell(0);
		    	text = createHelper.createRichTextString("SIN INFORMACION");
		    	text.applyFont(font);
		    	cell.setCellValue(text);
	    	}else{
	    		Font fontRed = workbook.createFont();
	    		fontRed.setFontHeightInPoints((short)10);
	    		fontRed.setColor(Font.COLOR_RED);
	    		font = workbook.createFont();
		    	font.setFontHeightInPoints((short)10);
	    		for (Object[] objects : data) {
	    			row = sheet.createRow(rowNumber++);
	    			cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] != null){
							switch(i){
							case 0:
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDD(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							case 6:
								text = createHelper.createRichTextString(WebUtilidades.getAsCurrencie(
										StringUtils.trimToEmpty(String.valueOf(objects[i]))).toString());
								break;
							case 7:
								text = createHelper.createRichTextString(StringUtils.substring(StringUtils.trimToEmpty(String.valueOf(objects[i])), 0, 4));
								break;
							default:
								text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							}
							cell.setCellValue(text);
							cell.setCellStyle(cellStyle);
						}else cell.setCellValue("");
						if("2".equals(String.valueOf(objects[10]))){
							text.applyFont(fontRed);
						}else{
							text.applyFont(font);
						}
					}
				}
	    	}
	    	
	    	HSSFPalette palette = workbook.getCustomPalette();
	    	palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
	    	palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
	    	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	    	HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
	        PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        FontFormatting fontFormatting = ruleHeader.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
	        fontFormatting.setFontStyle(false, true);
	        HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	
	    	HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
	    	fillFormatting = ruleRowsOdd.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsOdd.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsOdd.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);

	    	HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
	    	fillFormatting = ruleRowsEven.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsEven.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsEven.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:J" + (data.size() + 3))};

	        sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
	    	response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"VIGENCIA.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
	
	@RequestMapping(value = "/asegurados", method = {RequestMethod.GET, RequestMethod.POST})
	public void asegurados(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		String[] headers = {
    		"TIPO DE CLIENTE", "CLIENTE", "DUI", "NIT", "FECHA_NACIMIENTO", "ESTADO CIVIL", "PROFESION", "TELEFONO", 
    		"CELULAR", "FAX", "CORREO ELECTRONICO", "CORREO ELECTRONICO TRABAJO", 
    		"CORREO ELECTRONICO SECRETARIA", "DEPARTAMENTO", "MUNICIPIO", "COLONIA / CIUDAD",
    		"DIRECCION"
    	};

		try{
	    	CreationHelper createHelper = workbook.getCreationHelper();
	    	Sheet sheet = workbook.createSheet("ASEGURADOS");
	    	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
	    	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
	    	Row row = sheet.createRow(0);
	    	Cell cell = row.createCell(0);
	    	RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. ASEGURADOS");
	    	Font font = workbook.createFont();
	    	HSSFCellStyle cellStyle = workbook.createCellStyle();
	    	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)18);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(1);
	    	cell = row.createCell(0);
	    	text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
	    			" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(2);
	    	for (String header : headers) {
	    		sheet.autoSizeColumn(cellNumber);
		    	cell = row.createCell(cellNumber++);
		    	text = createHelper.createRichTextString(header);
		    	text.applyFont(font);
		    	cell.setCellValue(text);
		    	cell.setCellStyle(cellStyle);
			}
	    	List<Object[]> data = DBContext.getAsgReporteDao().findAsegurados(asgSeguroForm);
	    	cellNumber = 0;
	    	if(WebUtilidades.isEmptyList(data)){
	    		row = sheet.createRow(3);
	    		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
	    		cell = row.createCell(0);
		    	text = createHelper.createRichTextString("SIN INFORMACION");
		    	text.applyFont(font);
		    	cell.setCellValue(text);
	    	}else{
	    		font = workbook.createFont();
		    	font.setFontHeightInPoints((short)10);
	    		for (Object[] objects : data) {
	    			row = sheet.createRow(rowNumber++);
	    			cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] == null) cell.setCellValue("");
						else {
							if(i == 4){
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDD(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
							}else
								text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							text.applyFont(font);
							cell.setCellValue(text);
						}
					}
				}
	    	}
	    	
	    	HSSFPalette palette = workbook.getCustomPalette();
	    	palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
	    	palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
	    	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	    	HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
	        PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        FontFormatting fontFormatting = ruleHeader.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
	        fontFormatting.setFontStyle(false, true);
	        HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	
	    	HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
	    	fillFormatting = ruleRowsOdd.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsOdd.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsOdd.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);

	    	HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
	    	fillFormatting = ruleRowsEven.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsEven.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsEven.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:N" + (data.size() + 3))};

	        sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
	    	response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"ASEGURADOS.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
	
	@RequestMapping(value = "/asesores", method = {RequestMethod.GET, RequestMethod.POST})
	public void asesores(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		String[] headers = {
				"ASEGURADORA", "NOMBRE COMPLETO", "DUI", "NIT", "TELEFONO", 
				"CELULAR", "CORREO ELECTRONICO", "DIRECCION"
		};
		
		try{
			CreationHelper createHelper = workbook.getCreationHelper();
			Sheet sheet = workbook.createSheet("ASESORES");
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
			Row row = sheet.createRow(0);
			Cell cell = row.createCell(0);
			RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. ASESORES");
			Font font = workbook.createFont();
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)18);
			text.applyFont(font);
			cell.setCellValue(text);
			cell.setCellStyle(cellStyle);
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			row = sheet.createRow(1);
			cell = row.createCell(0);
			text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
					" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			text.applyFont(font);
			cell.setCellValue(text);
			cell.setCellStyle(cellStyle);
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			row = sheet.createRow(2);
			for (String header : headers) {
				sheet.autoSizeColumn(cellNumber);
				cell = row.createCell(cellNumber++);
				text = createHelper.createRichTextString(header);
				text.applyFont(font);
				cell.setCellValue(text);
				cell.setCellStyle(cellStyle);
			}
			List<Object[]> data = DBContext.getAsgReporteDao().findAsesores(asgSeguroForm);
			cellNumber = 0;
			if(WebUtilidades.isEmptyList(data)){
				row = sheet.createRow(3);
				sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
				cell = row.createCell(0);
				text = createHelper.createRichTextString("SIN INFORMACION");
				text.applyFont(font);
				cell.setCellValue(text);
			}else{
				font = workbook.createFont();
		    	font.setFontHeightInPoints((short)10);
				for (Object[] objects : data) {
					row = sheet.createRow(rowNumber++);
					cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] == null) cell.setCellValue("");
						else {
							text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							text.applyFont(font);
							cell.setCellValue(text);
						}
					}
				}
			}
			
			HSSFPalette palette = workbook.getCustomPalette();
			palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
			palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
			SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
			HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
			PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			FontFormatting fontFormatting = ruleHeader.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
			fontFormatting.setFontStyle(false, true);
			HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			
			HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
			fillFormatting = ruleRowsOdd.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			fontFormatting = ruleRowsOdd.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
			fontFormatting.setFontStyle(false, false);
			borderFormatting = ruleRowsOdd.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			
			HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
			fillFormatting = ruleRowsEven.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			fontFormatting = ruleRowsEven.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
			fontFormatting.setFontStyle(false, false);
			borderFormatting = ruleRowsEven.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:H" + (data.size() + 3))};
			
			sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
			response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"ASESORES.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
	
	@RequestMapping(value = "/polizasVendidas", method = {RequestMethod.GET, RequestMethod.POST})
	public void polizasVendidas(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		String[] headers = {
    		"FECHA DE INICIO", "CLIENTE", "POLIZA", "ASEGURADORA", "TIPO DE SEGURO",
    		"PLAN", "ASESOR", "PRIMA ANUAL" 
    	};

		try{
	    	CreationHelper createHelper = workbook.getCreationHelper();
	    	Sheet sheet = workbook.createSheet("POLIZAS_VENDIDAS");
	    	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
	    	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
	    	Row row = sheet.createRow(0);
	    	Cell cell = row.createCell(0);
	    	RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. POLIZAS VENDIDAS");
	    	Font font = workbook.createFont();
	    	HSSFCellStyle cellStyle = workbook.createCellStyle();
	    	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)18);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(1);
	    	cell = row.createCell(0);
	    	text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
	    			" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(2);
	    	for (String header : headers) {
	    		sheet.autoSizeColumn(cellNumber);
		    	cell = row.createCell(cellNumber++);
		    	text = createHelper.createRichTextString(header);
		    	text.applyFont(font);
		    	cell.setCellValue(text);
		    	cell.setCellStyle(cellStyle);
			}
	    	List<Object[]> data = DBContext.getAsgReporteDao().findPolizasVendidas(asgSeguroForm);
	    	cellNumber = 0;
	    	if(WebUtilidades.isEmptyList(data)){
	    		row = sheet.createRow(3);
	    		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
	    		cell = row.createCell(0);
		    	text = createHelper.createRichTextString("SIN INFORMACION");
		    	text.applyFont(font);
		    	cell.setCellValue(text);
	    	}else{
	    		font = workbook.createFont();
		    	font.setFontHeightInPoints((short)10);
	    		for (Object[] objects : data) {
	    			row = sheet.createRow(rowNumber++);
	    			cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] != null){
							switch(i){
							case 0:
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDD(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							case 7:
								text = createHelper.createRichTextString(WebUtilidades.getAsCurrencie(
										StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							default:
								text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							}
							cell.setCellValue(text);
							cell.setCellStyle(cellStyle);
						}else cell.setCellValue("");
				    	text.applyFont(font);
					}
				}
	    	}
	    	
	    	HSSFPalette palette = workbook.getCustomPalette();
	    	palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
	    	palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
	    	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	    	HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
	        PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        FontFormatting fontFormatting = ruleHeader.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
	        fontFormatting.setFontStyle(false, true);
	        HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	
	    	HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
	    	fillFormatting = ruleRowsOdd.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsOdd.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsOdd.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);

	    	HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
	    	fillFormatting = ruleRowsEven.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsEven.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsEven.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:H" + (data.size() + 3))};

	        sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
	    	response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"POLIZAS VENDIDAS.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
	
	@RequestMapping(value = "/polizasPorCliente", method = {RequestMethod.GET, RequestMethod.POST})
	public void polizasPorCliente(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		int rowNumberTemp = 3;
		Long asgClienteId = null;
		String[] headers = {
    		"CLIENTE", "POLIZA", "ASEGURADORA", "TIPO DE SEGURO", "ASESOR", 
    		"CUOTAS", "PRIMA ANUAL", "VIGENCIA", "RENOVACION", "ESTADO"
    	};

		try{
	    	CreationHelper createHelper = workbook.getCreationHelper();
	    	Sheet sheet = workbook.createSheet("POLIZAS POR CLIENTE");
	    	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
	    	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
	    	Row row = sheet.createRow(0);
	    	Cell cell = row.createCell(0);
	    	RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. POLIZAS POR CLIENTE");
	    	Font font = workbook.createFont();
	    	HSSFCellStyle cellStyle = workbook.createCellStyle();
	    	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)18);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(1);
	    	cell = row.createCell(0);
	    	text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
	    			" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(2);
	    	for (String header : headers) {
	    		sheet.autoSizeColumn(cellNumber);
		    	cell = row.createCell(cellNumber++);
		    	text = createHelper.createRichTextString(header);
		    	text.applyFont(font);
		    	cell.setCellValue(text);
		    	cell.setCellStyle(cellStyle);
			}
	    	List<Object[]> data = DBContext.getAsgReporteDao().findPolizasPorCliente(asgSeguroForm);
	    	cellNumber = 0;
	    	if(WebUtilidades.isEmptyList(data)){
	    		row = sheet.createRow(3);
	    		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
	    		cell = row.createCell(0);
		    	text = createHelper.createRichTextString("SIN INFORMACION");
		    	text.applyFont(font);
		    	cell.setCellValue(text);
	    	}else{
	    		font = workbook.createFont();
		    	font.setFontHeightInPoints((short)10);
	    		for (Object[] objects : data) {
	    			if(objects[11] != null && !objects[11].equals(asgClienteId)){
	    				if(asgClienteId != null){
	    					sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
	    					rowNumberTemp = rowNumber;
	    				}
	    				asgClienteId = (Long) objects[11];
	    			}
	    			row = sheet.createRow(rowNumber++);
	    			cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] != null){
							switch(i){
							case 6:
								text = createHelper.createRichTextString(WebUtilidades.getAsCurrencie(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							case 7:
							case 8:
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDD(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							default:
								text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							}
							cell.setCellValue(text);
							cell.setCellStyle(cellStyle);
						}else cell.setCellValue("");
				    	text.applyFont(font);
					}
				}
	    		sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
	    	}
	    	
	    	HSSFPalette palette = workbook.getCustomPalette();
	    	palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
	    	palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
	    	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	    	HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
	        PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        FontFormatting fontFormatting = ruleHeader.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
	        fontFormatting.setFontStyle(false, true);
	        HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	
	    	HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
	    	fillFormatting = ruleRowsOdd.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsOdd.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsOdd.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);

	    	HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
	    	fillFormatting = ruleRowsEven.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsEven.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsEven.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:J" + (data.size() + 3))};

	        sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
	    	response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"POLIZAS POR CLIENTE.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
	
	@RequestMapping(value = "/polizasPorAsesor", method = {RequestMethod.GET, RequestMethod.POST})
	public void polizasPorAsesor(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		int rowNumberTemp = 3;
		Long asgClienteId = null;
		String[] headers = {
			"ASESOR", "CLIENTE", "POLIZA", "ASEGURADORA", "TIPO DE SEGURO",  
    		"CUOTAS", "PRIMA ANUAL", "VIGENCIA", "RENOVACION", "ESTADO"
    	};

		try{
	    	CreationHelper createHelper = workbook.getCreationHelper();
	    	Sheet sheet = workbook.createSheet("POLIZAS POR ASESOR");
	    	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
	    	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
	    	Row row = sheet.createRow(0);
	    	Cell cell = row.createCell(0);
	    	RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. POLIZAS POR ASESOR");
	    	Font font = workbook.createFont();
	    	HSSFCellStyle cellStyle = workbook.createCellStyle();
	    	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)18);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(1);
	    	cell = row.createCell(0);
	    	text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
	    			" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(2);
	    	for (String header : headers) {
	    		sheet.autoSizeColumn(cellNumber);
		    	cell = row.createCell(cellNumber++);
		    	text = createHelper.createRichTextString(header);
		    	text.applyFont(font);
		    	cell.setCellValue(text);
		    	cell.setCellStyle(cellStyle);
			}
	    	List<Object[]> data = DBContext.getAsgReporteDao().findPolizasPorAsesor(asgSeguroForm);
	    	cellNumber = 0;
	    	if(WebUtilidades.isEmptyList(data)){
	    		row = sheet.createRow(3);
	    		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
	    		cell = row.createCell(0);
		    	text = createHelper.createRichTextString("SIN INFORMACION");
		    	text.applyFont(font);
		    	cell.setCellValue(text);
	    	}else{
	    		font = workbook.createFont();
		    	font.setFontHeightInPoints((short)10);
	    		for (Object[] objects : data) {
	    			if(objects[11] != null && !objects[11].equals(asgClienteId)){
	    				if(asgClienteId != null){
	    					sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
	    					rowNumberTemp = rowNumber;
	    				}
	    				asgClienteId = (Long) objects[11];
	    			}
	    			row = sheet.createRow(rowNumber++);
	    			cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] != null){
							switch(i){
							case 6:
								text = createHelper.createRichTextString(WebUtilidades.getAsCurrencie(
										StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							case 7:
							case 8:
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDD(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							default:
								text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							}
							cell.setCellValue(text);
							cell.setCellStyle(cellStyle);
						}else cell.setCellValue("");
				    	text.applyFont(font);
					}
				}
	    		sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
	    	}
	    	
	    	HSSFPalette palette = workbook.getCustomPalette();
	    	palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
	    	palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
	    	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	    	HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
	        PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        FontFormatting fontFormatting = ruleHeader.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
	        fontFormatting.setFontStyle(false, true);
	        HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	
	    	HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
	    	fillFormatting = ruleRowsOdd.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsOdd.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsOdd.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);

	    	HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
	    	fillFormatting = ruleRowsEven.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsEven.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsEven.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:J" + (data.size() + 3))};

	        sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
	    	response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"POLIZAS POR ASESOR.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
	
	@RequestMapping(value = "/reclamos", method = {RequestMethod.GET, RequestMethod.POST})
	public void reclamos(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		int rowNumberTemp = 3;
		Long asgClienteId = null;
		String[] headers = {
			"NO. POLIZA", "CLIENTE", "RESPONSABLE", "ASUNTO", "FECHA SEGUIMIENTO", "PROXIMO SEGUIMIENTO",  
    		"DESCRIPCION"
    	};

		try{
	    	CreationHelper createHelper = workbook.getCreationHelper();
	    	Sheet sheet = workbook.createSheet("RECLAMOS");
	    	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
	    	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
	    	Row row = sheet.createRow(0);
	    	Cell cell = row.createCell(0);
	    	RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. RECLAMOS");
	    	Font font = workbook.createFont();
	    	HSSFCellStyle cellStyle = workbook.createCellStyle();
	    	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)18);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(1);
	    	cell = row.createCell(0);
	    	text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
	    			" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	text.applyFont(font);
	    	cell.setCellValue(text);
	    	cell.setCellStyle(cellStyle);
	    	font = workbook.createFont();
	    	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    	font.setFontHeightInPoints((short)10);
	    	row = sheet.createRow(2);
	    	for (String header : headers) {
	    		sheet.autoSizeColumn(cellNumber);
		    	cell = row.createCell(cellNumber++);
		    	text = createHelper.createRichTextString(header);
		    	text.applyFont(font);
		    	cell.setCellValue(text);
		    	cell.setCellStyle(cellStyle);
			}
	    	List<Object[]> data = DBContext.getAsgReporteDao().findReclamos(asgSeguroForm);
	    	cellNumber = 0;
	    	if(WebUtilidades.isEmptyList(data)){
	    		row = sheet.createRow(3);
	    		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
	    		cell = row.createCell(0);
		    	text = createHelper.createRichTextString("SIN INFORMACION");
		    	text.applyFont(font);
		    	cell.setCellValue(text);
	    	}else{
	    		font = workbook.createFont();
		    	font.setFontHeightInPoints((short)10);
	    		for (Object[] objects : data) {
	    			if(objects[7] != null && !objects[7].equals(asgClienteId)){
	    				if(asgClienteId != null){
	    					sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
	    					sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 1, 1));
	    					sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 2, 2));
	    					sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 3, 3));
	    					rowNumberTemp = rowNumber;
	    				}
	    				asgClienteId = (Long) objects[7];
	    			}
	    			row = sheet.createRow(rowNumber++);
	    			cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] != null){
							switch(i){
							case 4:
							case 5:
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDD(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							default:
								text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							}
							cell.setCellValue(text);
							cell.setCellStyle(cellStyle);
						}else cell.setCellValue("");
				    	text.applyFont(font);
					}
				}
	    		sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
	    		sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 1, 1));
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 2, 2));
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 3, 3));
	    	}
	    	
	    	HSSFPalette palette = workbook.getCustomPalette();
	    	palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
	    	palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
	    	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	    	HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
	        PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        FontFormatting fontFormatting = ruleHeader.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
	        fontFormatting.setFontStyle(false, true);
	        HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	
	    	HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
	    	fillFormatting = ruleRowsOdd.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsOdd.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsOdd.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);

	    	HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
	    	fillFormatting = ruleRowsEven.createPatternFormatting();
	        fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
	        fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	        fontFormatting = ruleRowsEven.createFontFormatting();
	        fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
	        fontFormatting.setFontStyle(false, false);
	        borderFormatting = ruleRowsEven.createBorderFormatting();
	    	borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
	    	borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
	    	CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:G" + (data.size() + 3))};

	        sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
	    	response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"RECLAMOS.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
	
	@RequestMapping(value = "/pendientes", method = {RequestMethod.GET, RequestMethod.POST})
	public void pendientes(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		int rowNumberTemp = 3;
		Long asgClienteId = null;
		String[] headers = {
				"NO. POLIZA", "CLIENTE", "RESPONSABLE", "ASUNTO", "FECHA SEGUIMIENTO", "PROXIMO SEGUIMIENTO",  
				"DESCRIPCION"
		};
		
		try{
			CreationHelper createHelper = workbook.getCreationHelper();
			Sheet sheet = workbook.createSheet("PENDIENTES");
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
			Row row = sheet.createRow(0);
			Cell cell = row.createCell(0);
			RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. PENDIENTES");
			Font font = workbook.createFont();
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)18);
			text.applyFont(font);
			cell.setCellValue(text);
			cell.setCellStyle(cellStyle);
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			row = sheet.createRow(1);
			cell = row.createCell(0);
			text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
					" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			text.applyFont(font);
			cell.setCellValue(text);
			cell.setCellStyle(cellStyle);
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			row = sheet.createRow(2);
			for (String header : headers) {
				sheet.autoSizeColumn(cellNumber);
				cell = row.createCell(cellNumber++);
				text = createHelper.createRichTextString(header);
				text.applyFont(font);
				cell.setCellValue(text);
				cell.setCellStyle(cellStyle);
			}
			List<Object[]> data = DBContext.getAsgReporteDao().findPendientes(asgSeguroForm);
			cellNumber = 0;
			if(WebUtilidades.isEmptyList(data)){
				row = sheet.createRow(3);
				sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
				cell = row.createCell(0);
				text = createHelper.createRichTextString("SIN INFORMACION");
				text.applyFont(font);
				cell.setCellValue(text);
			}else{
				font = workbook.createFont();
		    	font.setFontHeightInPoints((short)10);
				for (Object[] objects : data) {
					if(objects[7] != null && !objects[7].equals(asgClienteId)){
						if(asgClienteId != null){
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 1, 1));
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 2, 2));
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 3, 3));
							rowNumberTemp = rowNumber;
						}
						asgClienteId = (Long) objects[7];
					}
					row = sheet.createRow(rowNumber++);
					cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] != null){
							switch(i){
							case 4:
							case 5:
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDD(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							default:
								text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							}
							cell.setCellValue(text);
							cell.setCellStyle(cellStyle);
						}else cell.setCellValue("");
						text.applyFont(font);
					}
				}
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 1, 1));
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 2, 2));
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 3, 3));
			}
			
			HSSFPalette palette = workbook.getCustomPalette();
			palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
			palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
			SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
			HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
			PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			FontFormatting fontFormatting = ruleHeader.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
			fontFormatting.setFontStyle(false, true);
			HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			
			HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
			fillFormatting = ruleRowsOdd.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			fontFormatting = ruleRowsOdd.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
			fontFormatting.setFontStyle(false, false);
			borderFormatting = ruleRowsOdd.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			
			HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
			fillFormatting = ruleRowsEven.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			fontFormatting = ruleRowsEven.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
			fontFormatting.setFontStyle(false, false);
			borderFormatting = ruleRowsEven.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:G" + (data.size() + 3))};
			
			sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
			response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"PENDIENTES.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
	
	@RequestMapping(value = "/ventas", method = {RequestMethod.GET, RequestMethod.POST})
	public void ventas(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		int rowNumberTemp = 3;
		Long asgClienteId = null;
		String[] headers = {
				"CLIENTE", "TIPO DE SEGURO SOLICITADO", "RESPONSABLE", "ASUNTO", "FECHA SEGUIMIENTO", "PROXIMO SEGUIMIENTO",  
				"DESCRIPCION"
		};
		
		try{
			CreationHelper createHelper = workbook.getCreationHelper();
			Sheet sheet = workbook.createSheet("VENTAS");
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
			Row row = sheet.createRow(0);
			Cell cell = row.createCell(0);
			RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. VENTAS");
			Font font = workbook.createFont();
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)18);
			text.applyFont(font);
			cell.setCellValue(text);
			cell.setCellStyle(cellStyle);
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			row = sheet.createRow(1);
			cell = row.createCell(0);
			text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
					" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			text.applyFont(font);
			cell.setCellValue(text);
			cell.setCellStyle(cellStyle);
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			row = sheet.createRow(2);
			for (String header : headers) {
				sheet.autoSizeColumn(cellNumber);
				cell = row.createCell(cellNumber++);
				text = createHelper.createRichTextString(header);
				text.applyFont(font);
				cell.setCellValue(text);
				cell.setCellStyle(cellStyle);
			}
			List<Object[]> data = DBContext.getAsgReporteDao().findVentas(asgSeguroForm);
			cellNumber = 0;
			if(WebUtilidades.isEmptyList(data)){
				row = sheet.createRow(3);
				sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
				cell = row.createCell(0);
				text = createHelper.createRichTextString("SIN INFORMACION");
				text.applyFont(font);
				cell.setCellValue(text);
			}else{
				font = workbook.createFont();
		    	font.setFontHeightInPoints((short)10);
				for (Object[] objects : data) {
					if(objects[7] != null && !objects[7].equals(asgClienteId)){
						if(asgClienteId != null){
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 1, 1));
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 2, 2));
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 3, 3));
							rowNumberTemp = rowNumber;
						}
						asgClienteId = (Long) objects[7];
					}
					row = sheet.createRow(rowNumber++);
					cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] != null){
							switch(i){
							case 4:
							case 5:
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDD(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							default:
								text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							}
							cell.setCellValue(text);
							cell.setCellStyle(cellStyle);
						}else cell.setCellValue("");
						text.applyFont(font);
					}
				}
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 1, 1));
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 2, 2));
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 3, 3));
			}
			
			HSSFPalette palette = workbook.getCustomPalette();
			palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
			palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
			SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
			HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
			PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			FontFormatting fontFormatting = ruleHeader.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
			fontFormatting.setFontStyle(false, true);
			HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			
			HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
			fillFormatting = ruleRowsOdd.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			fontFormatting = ruleRowsOdd.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
			fontFormatting.setFontStyle(false, false);
			borderFormatting = ruleRowsOdd.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			
			HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
			fillFormatting = ruleRowsEven.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			fontFormatting = ruleRowsEven.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
			fontFormatting.setFontStyle(false, false);
			borderFormatting = ruleRowsEven.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:G" + (data.size() + 3))};
			
			sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
			response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"VENTAS.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
	
	@RequestMapping(value = "/abonos", method = {RequestMethod.GET, RequestMethod.POST})
	public void abonos(HttpServletResponse response, 
			@ModelAttribute AsgSeguroForm asgSeguroForm) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		int cellNumber = 0;
		int rowNumber = 3;
		int rowNumberTemp = 3;
		Long asgClienteId = null;
		String[] headers = {
				"POLIZA", "CLIENTE", "NO. CUOTA", "VALOR DE LA CUOTA", "FECHA LIMITE", "ESTADO",  
				"FECHA DE PAGO"
		};
		
		try{
			CreationHelper createHelper = workbook.getCreationHelper();
			Sheet sheet = workbook.createSheet("ESTADO DE CUENTA");
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
			Row row = sheet.createRow(0);
			Cell cell = row.createCell(0);
			RichTextString text = createHelper.createRichTextString("CREDISEGUROS, S.A. DE C.V. .::. ESTADO DE CUENTA");
			Font font = workbook.createFont();
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)18);
			text.applyFont(font);
			cell.setCellValue(text);
			cell.setCellStyle(cellStyle);
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			row = sheet.createRow(1);
			cell = row.createCell(0);
			text = createHelper.createRichTextString("USUARIO: " + WebUtilidades.getSgdUsuarioFromAcegi().getSgdUsuarioNombreCompleto() + 
					" .::. FECHA: " + WebUtilidades.dateFormatAsDD_MM_YYYY.format(new Date()));
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			text.applyFont(font);
			cell.setCellValue(text);
			cell.setCellStyle(cellStyle);
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontHeightInPoints((short)10);
			row = sheet.createRow(2);
			for (String header : headers) {
				sheet.autoSizeColumn(cellNumber);
				cell = row.createCell(cellNumber++);
				text = createHelper.createRichTextString(header);
				text.applyFont(font);
				cell.setCellValue(text);
				cell.setCellStyle(cellStyle);
			}
			List<Object[]> data = DBContext.getAsgReporteDao().findAbonos(asgSeguroForm);
			cellNumber = 0;
			if(WebUtilidades.isEmptyList(data)){
				row = sheet.createRow(3);
				sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, headers.length - 1));
				cell = row.createCell(0);
				text = createHelper.createRichTextString("SIN INFORMACION");
				text.applyFont(font);
				cell.setCellValue(text);
			}else{
				font = workbook.createFont();
				font.setFontHeightInPoints((short)10);
				for (Object[] objects : data) {
					if(objects[7] != null && !objects[7].equals(asgClienteId)){
						if(asgClienteId != null){
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
							sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 1, 1));
							rowNumberTemp = rowNumber;
						}
						asgClienteId = (Long) objects[7];
					}
					row = sheet.createRow(rowNumber++);
					cellNumber = 0;
					for (int i = 0; i < objects.length; i++) {
						if(cellNumber >= headers.length) break;
						cell = row.createCell(cellNumber++);
						if(objects[i] != null){
							switch(i){
							case 3:
								text = createHelper.createRichTextString(WebUtilidades.getAsCurrencie(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							case 4:
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDD(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							case 6:
								text = createHelper.createRichTextString(WebUtilidades.getDateDD_MM_YYYY_From_YYYYMMDDHHMISS(StringUtils.trimToEmpty(String.valueOf(objects[i]))));
								break;
							default:
								text = createHelper.createRichTextString(StringUtils.trimToEmpty(String.valueOf(objects[i])));
							}
							cell.setCellValue(text);
							cell.setCellStyle(cellStyle);
						}else cell.setCellValue("");
						text.applyFont(font);
					}
				}
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 0, 0));
				sheet.addMergedRegion(new CellRangeAddress(rowNumberTemp, rowNumber - 1, 1, 1));
			}
			
			HSSFPalette palette = workbook.getCustomPalette();
			palette.setColorAtIndex(IndexedColors.DARK_BLUE.index, (byte)79, (byte)129, (byte)189);
			palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)219, (byte)229, (byte)241);
			SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
			HSSFConditionalFormattingRule ruleHeader = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("ROW()=3");
			PatternFormatting fillFormatting = ruleHeader.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.DARK_BLUE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			FontFormatting fontFormatting = ruleHeader.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.WHITE.index);
			fontFormatting.setFontStyle(false, true);
			HSSFBorderFormatting borderFormatting = ruleHeader.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			
			HSSFConditionalFormattingRule ruleRowsOdd = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 0), (ROW() > 3))");
			fillFormatting = ruleRowsOdd.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.BLUE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			fontFormatting = ruleRowsOdd.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
			fontFormatting.setFontStyle(false, false);
			borderFormatting = ruleRowsOdd.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			
			HSSFConditionalFormattingRule ruleRowsEven = (HSSFConditionalFormattingRule) sheetCF.createConditionalFormattingRule("AND((MOD(ROW(),2) = 1), (ROW() > 3))");
			fillFormatting = ruleRowsEven.createPatternFormatting();
			fillFormatting.setFillBackgroundColor(IndexedColors.WHITE.index);
			fillFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
			fontFormatting = ruleRowsEven.createFontFormatting();
			fontFormatting.setFontColorIndex(IndexedColors.BLACK.index);
			fontFormatting.setFontStyle(false, false);
			borderFormatting = ruleRowsEven.createBorderFormatting();
			borderFormatting.setBorderTop(CellStyle.BORDER_THIN);
			borderFormatting.setBorderBottom(CellStyle.BORDER_THIN);
			borderFormatting.setBorderLeft(CellStyle.BORDER_THIN);
			borderFormatting.setBorderRight(CellStyle.BORDER_THIN);
			CellRangeAddress[] regions = {CellRangeAddress.valueOf("A3:G" + (data.size() + 3))};
			
			sheetCF.addConditionalFormatting(regions, new ConditionalFormattingRule[]{ruleHeader, ruleRowsOdd, ruleRowsEven});  
			response.setContentType("application/xls");
			response.setHeader("Content-Disposition", "inline; filename=\"ESTADO DE CUENTA.xls\"");
			workbook.write(response.getOutputStream());
		} catch (Exception e) {
			logger.error("Exception: ", e);
			try {
				response.getWriter().write(e instanceof WebException ? e.getMessage() : WebUtilidades.PDF_ERROR);
			} catch (Exception e1) {
				logger.error(e1);
			}
		}
	}
}
